package com.liuyjy.excel.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.util.StringUtils;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URL;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author liuyjy
 * @Date 2022/3/22 14:44
 * @Description: ExcelUtil
 **/
@Slf4j
public class ExcelUtil {

    /**
     * 导出
     *
     * @param data     数据
     * @param fileName 文件名
     * @param clazz    对象
     */
    public static MultipartFile writeFile(List<? extends Object> data, String fileName, Class clazz) {
        DiskFileItemFactory diskFileItemFactory = new DiskFileItemFactory(16, null);
        FileItem item = diskFileItemFactory.createItem(
                "field",
                "application/vnd.ms-excel;charset=utf-8",
                true,
                fileName + ".xlsx");

        OutputStream outputStream = null;
        try {
            outputStream = item.getOutputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }
        //表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置内容靠左对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

        EasyExcel.write(outputStream, clazz).excelType(ExcelTypeEnum.XLSX).sheet("sheet1")
                .registerWriteHandler(horizontalCellStyleStrategy).doWrite(data);

        return new CommonsMultipartFile(item);
    }

    /**
     * 导出
     *
     * @param response 返回体
     * @param data     数据
     * @param fileName 文件名
     * @param clazz    对象
     */
    public static void writeExcel(HttpServletResponse response, List<? extends Object> data, String fileName, Class clazz) {
        writeExcel(response, data, fileName, "sheet1", clazz);
    }

    /**
     * 导出
     *
     * @param response  返回体
     * @param data      数据
     * @param fileName  文件名
     * @param sheetName sheet名
     * @param clazz     对象
     */
    public static void writeExcel(HttpServletResponse response, List<? extends Object> data, String fileName, String sheetName, Class clazz) {
        //表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置内容靠左对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        OutputStream outputStream = null;
        try {
            outputStream = getOutputStream(fileName, response);
            if (null == outputStream) {
                log.error("流获取失败！");
                return;
            }
            EasyExcel.write(getOutputStream(fileName, response), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).registerWriteHandler(horizontalCellStyleStrategy).doWrite(data);

        } finally {
            if (null != outputStream) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }


    }

    /**
     * 读取某个 sheet 的 Excel
     *
     * @param inputStream 流
     * @param clazz       clazz
     * @return Excel 数据 list
     */
    public static <T> List<T> readExcel(InputStream inputStream, Class clazz) {
        List<ReadSheet> readSheetList = new ArrayList<>();
        ReadSheet readSheet = new ReadSheet();
        //readSheet.setSheetNo(sheetNo);
        // readSheet.setHeadRowNumber(headLineNum);
        readSheet.setClazz(clazz);
        readSheetList.add(readSheet);
        return readExcel(inputStream, readSheetList);
    }

    /**
     * 读取某个 sheet 的 Excel
     *
     * @param excel 文件
     * @param clazz clazz
     * @return Excel 数据 list
     */
    public static <T> List<T> readExcel(MultipartFile excel, Class clazz) {
        List<ReadSheet> readSheetList = new ArrayList<>();
        ReadSheet readSheet = new ReadSheet();
//        readSheet.setSheetNo(sheetNo);
//        readSheet.setHeadRowNumber(headLineNum);
        readSheet.setClazz(clazz);
        readSheetList.add(readSheet);
        return readExcel(excel, readSheetList);
    }

    /**
     * 读取指定 sheet 的 Excel
     *
     * @param excel         文件
     * @param readSheetList Sheet
     * @return Excel 数据 list
     */
    public static <T> List<T> readExcel(InputStream excel, List<ReadSheet> readSheetList) {
        ExcelListener<T> excelListener = new ExcelListener<T>();

        ExcelReader reader = getReader(excel, excelListener);
        if (reader == null) {
            return null;
        }
        reader.read(readSheetList);
        return excelListener.getDatas();
    }

    /**
     * 读取某个 sheet 的 Excel
     *
     * @param excel       文件
     * @param clazz       clazz
     * @param sheetNo     sheet 的序号 从1开始
     * @param headLineNum 表头行数，默认为1
     * @return Excel 数据 list
     */
    public static <T> List<T> readExcel(MultipartFile excel, Class clazz, int sheetNo, int headLineNum) {
        List<ReadSheet> readSheetList = new ArrayList<>();
        ReadSheet readSheet = new ReadSheet();
        readSheet.setSheetNo(sheetNo);
        readSheet.setHeadRowNumber(headLineNum);
        readSheet.setClazz(clazz);
        readSheetList.add(readSheet);
        return readExcel(excel, readSheetList);
    }

    /**
     * 读取某个 sheet 的 Excel
     *
     * @param filePath    需要解析的 Excel 文件路径
     * @param clazz       clazz
     * @param sheetNo     sheet 的序号 从1开始
     * @param headLineNum 表头行数，默认为1
     * @return Excel 数据 list
     */
    public static <T> List<T> readExcel(String filePath, Class clazz, int sheetNo, int headLineNum) {
        List<ReadSheet> readSheetList = new ArrayList<>();
        ReadSheet readSheet = new ReadSheet();
        readSheet.setSheetNo(sheetNo);
        readSheet.setHeadRowNumber(headLineNum);
        readSheet.setClazz(clazz);
        readSheetList.add(readSheet);
        return readExcel(filePath, readSheetList);
    }

    /**
     * 读取所有
     *
     * @param excel 文件
     * @return Excel 数据 list
     */
    private static <T> List<T> readExcel(MultipartFile excel) {

        ExcelListener<T> excelListener = new ExcelListener<T>();
        ExcelReader reader = getReader(excel, excelListener);
        if (reader == null) {
            return null;
        }
        reader.readAll();
        return excelListener.getDatas();
    }

    /**
     * 读取所有
     *
     * @param filePath 需要解析的 Excel 文件路径
     * @return Excel 数据 list
     */
    public static <T> List<T> readExcel(String filePath, Class<T> clazz) {

        ExcelListener<T> excelListener = new ExcelListener<T>();
        ExcelReader reader = getReader(filePath, excelListener);
        if (reader == null) {
            return null;
        }
        reader.readAll();
        return excelListener.getDatas();
    }

    /**
     * 读取所有
     *
     * @param filePath 需要解析的 Excel 文件路径
     * @return Excel 数据 list
     */
    private static <T> List<T> readExcel(String filePath) {

        ExcelListener<T> excelListener = new ExcelListener<T>();
        ExcelReader reader = getReader(filePath, excelListener);
        if (reader == null) {
            return null;
        }
        reader.readAll();
        return excelListener.getDatas();
    }

    /**
     * 读取指定 sheet 的 Excel
     *
     * @param excel         文件
     * @param readSheetList Sheet
     * @return Excel 数据 list
     */
    public static <T> List<T> readExcel(MultipartFile excel, List<ReadSheet> readSheetList) {
        ExcelListener<T> excelListener = new ExcelListener<T>();

        ExcelReader reader = getReader(excel, excelListener);
        if (reader == null) {
            return null;
        }
        reader.read(readSheetList);
        return excelListener.getDatas();
    }

    /**
     * 读取指定 sheet 的 Excel
     *
     * @param filePath      需要解析的 Excel 文件路径
     * @param readSheetList Sheet
     * @return Excel 数据 list
     */
    public static <T> List<T> readExcel(String filePath, List<ReadSheet> readSheetList) {
        ExcelListener<T> excelListener = new ExcelListener<T>();

        ExcelReader reader = getReader(filePath, excelListener);
        if (reader == null) {
            return null;
        }
        reader.read(readSheetList);
        return excelListener.getDatas();
    }

    /**
     * 返回 ExcelReader
     *
     * @param inputStream   流
     * @param excelListener new ExcelListener()
     */
    private static ExcelReader getReader(InputStream inputStream, ExcelListener excelListener) {
        return EasyExcel.read(inputStream, excelListener).build();
    }

    /**
     * 返回 ExcelReader
     *
     * @param filePath      需要解析的 Excel 文件路径
     * @param excelListener new ExcelListener()
     */
    private static ExcelReader getReader(String filePath, ExcelListener excelListener) {
        if (StringUtils.isBlank(filePath)) {
            throw new RuntimeException("找不到文件！");
        }
        if (!filePath.toLowerCase().endsWith(".xls") && !filePath.toLowerCase().endsWith(".xlsx")) {
            throw new RuntimeException("文件格式错误！");
        }
        InputStream inputStream = null;
        try {
            URL url = new URL(filePath);
            inputStream = new BufferedInputStream(url.openStream());
            return EasyExcel.read(inputStream, excelListener).build();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (null != inputStream) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }

        }
        return null;
    }

    /**
     * 返回 ExcelReader
     *
     * @param excel         需要解析的 Excel 文件
     * @param excelListener new ExcelListener()
     */
    private static ExcelReader getReader(MultipartFile excel, ExcelListener excelListener) {
        String filename = excel.getOriginalFilename();

        if (filename == null || (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
            throw new RuntimeException("文件格式错误！");
        }
        InputStream inputStream = null;
        try {
            inputStream = new BufferedInputStream(excel.getInputStream());
            return EasyExcel.read(inputStream, excelListener).build();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (null != inputStream) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }

        }
        return null;
    }


    /**
     * 导出文件时为Writer生成OutputStream
     *
     * @param fileName 文件名
     * @param response 返回体
     * @return OutputStream
     */
    private static OutputStream getOutputStream(String fileName, HttpServletResponse response) {
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8");
        } catch (UnsupportedEncodingException e) {
            log.info("文件名encode失败！");
            e.printStackTrace();
        }
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
        response.addHeader("Access-Control-Expose-Headers", "Content-disposition");
        ServletOutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
        } catch (IOException e) {
            log.info("IO处理失败！");
            e.printStackTrace();
        }
        return outputStream;
    }

}
